package com.spring.Connection;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import javax.sql.DataSource;

public class UserDao {
	private DataSource dataSource = null;
    
    public void setDataSource(DataSource dataSource){
    	this.dataSource = dataSource;
    }
    
	
	public void createTable()throws SQLException{
		Connection conn = dataSource.getConnection();
		
		StringBuffer query = null;

		
		conn.setAutoCommit(false);
		query = new StringBuffer();
		query.append("CREATE TABLE member(userid VARCHAR(45), ");
		query.append("password VARCHAR(45), name VARCHAR(45))");
		conn.createStatement().executeUpdate(query.toString());
		conn.commit();
		
		System.out.println("----------------- 테이블 생성");
		System.out.println("테이블 생성 완료");
		
		conn.close();
			
	}
		
	public void insertUser(User user)throws SQLException{
		Connection conn = dataSource.getConnection();
		
		PreparedStatement pstmt = null;
		StringBuffer query = null;
				
		conn.setAutoCommit(false);
		query = new StringBuffer();
		query.append("INSERT INTO member VALUES(?, ?, ?)");
		pstmt = conn.prepareStatement(query.toString());
		pstmt.clearParameters();
		pstmt.setString(1, user.getUserid());
		pstmt.setString(2, user.getPasssword());
		pstmt.setString(3, user.getName());
		pstmt.executeUpdate();
		
		conn.commit();
		
		pstmt.close();
		
		System.out.println("----------------- 테이블 값 입력");
		System.out.println(user.getUserid()+", 등록 완료");
		conn.close();
	}
	
	public void selectUser(String userid)throws SQLException{
		Connection conn = dataSource.getConnection();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = null;
		
		query = new StringBuffer();
		query.append("SELECT * FROM member WHERE USERID = ?");
		
		pstmt = conn.prepareStatement(query.toString());
		pstmt.clearParameters();
		pstmt.setString(1, userid);
		rs = pstmt.executeQuery();
		
		System.out.println("----------------- 테이블 값 조회");
		while(rs.next()){
			System.out.print(rs.getString(1)+", ");
			System.out.print(rs.getString(2)+", ");
			System.out.println(rs.getString(3));
		}
								
		rs.close();
		pstmt.close();
		conn.close();
	}
	
	public void deleteUser(String userid)throws SQLException{
		Connection conn = dataSource.getConnection();
		
		PreparedStatement pstmt = null;
		StringBuffer query = null;

		conn.setAutoCommit(false);
		query = new StringBuffer();
		query.append("DELETE FROM member WHERE USERID = ?");
		pstmt = conn.prepareStatement(query.toString());
		pstmt.clearParameters();
		pstmt.setString(1, userid);
		pstmt.executeUpdate();
		
		conn.commit();
		
		pstmt.close();
		
		System.out.println("----------------- 테이블 값 삭제");
		System.out.println(userid + ", 삭제 완료");
		conn.close();
	}
	
	public void updateUser(User user)throws SQLException{
		Connection conn = dataSource.getConnection();
		
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		StringBuffer query = null;
		
		conn.setAutoCommit(false);
		query = new StringBuffer();
		query.append("UPDATE member SET PASSWORD = ?, ");
		query.append("NAME = ? WHERE USERID = ?");
		pstmt = conn.prepareStatement(query.toString());
		pstmt.clearParameters();
		pstmt.setString(1, user.getPasssword());
		pstmt.setString(2, user.getName());
		pstmt.setString(3, user.getUserid());
		pstmt.executeUpdate();
		
		conn.commit();
		
		pstmt.close();
					
		System.out.println("----------------- 테이블 값 갱신");
		System.out.println("비번, 이름 갱신 완료");
		conn.close();
	}
}
